<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en-us" lang="en-us">
<head>
  <link href="//gmpg.org/xfn/11" rel="profile">
  <meta http-equiv="content-type" content="text/html; charset=utf-8">
  <meta name="generator" content="Hugo 0.68.3" />

  
  <meta name="viewport" content="width=device-width, initial-scale=1.0">

  <title>sql语句查询特定时间段内的数据 &middot; 我的博客</title>

  
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/print.css" media="print">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/poole.css">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/syntax.css">
  <link type="text/css" rel="stylesheet" href="/my_technology_blog/css/hyde.css">
    <link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Abril+Fatface|PT+Sans:400,400i,700">


  
  <link rel="apple-touch-icon-precomposed" sizes="144x144" href="/apple-touch-icon-144-precomposed.png">
  <link rel="shortcut icon" href="/favicon.png">

  
  
</head>

  <body class="theme-base-0b ">
  <aside class="sidebar">
  <div class="container sidebar-sticky">
    <div class="sidebar-about">
      <a href="/my_technology_blog/"><h1>我的博客</h1></a>
      <p class="lead">
       杨博的博客 
      </p>
    </div>

    <nav>
      <ul class="sidebar-nav">
        <li><a href="/my_technology_blog/">Home</a> </li>
        
      </ul>
    </nav>

    <p>&copy; 2021. All rights reserved. </p>
  </div>
</aside>

    <main class="content container">
    <div class="post">
  <h1>sql语句查询特定时间段内的数据</h1>
  <time datetime=2020-05-27T20:03:59&#43;0800 class="post-date">Wed, May 27, 2020</time>
  <p><strong>数据库</strong></p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#66d9ef">SET</span> FOREIGN_KEY_CHECKS<span style="color:#f92672">=</span><span style="color:#ae81ff">0</span>;  
  
<span style="color:#75715e">-- ----------------------------  
</span><span style="color:#75715e">-- Table structure for t_user  
</span><span style="color:#75715e">-- ----------------------------  
</span><span style="color:#75715e"></span><span style="color:#66d9ef">DROP</span> <span style="color:#66d9ef">TABLE</span> <span style="color:#66d9ef">IF</span> <span style="color:#66d9ef">EXISTS</span> <span style="color:#f92672">`</span>t_user<span style="color:#f92672">`</span>;  
<span style="color:#66d9ef">CREATE</span> <span style="color:#66d9ef">TABLE</span> <span style="color:#f92672">`</span>t_user<span style="color:#f92672">`</span> (  
  <span style="color:#f92672">`</span>userId<span style="color:#f92672">`</span> bigint(<span style="color:#ae81ff">20</span>) <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,  
  <span style="color:#f92672">`</span>fullName<span style="color:#f92672">`</span> varchar(<span style="color:#ae81ff">64</span>) <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,  
  <span style="color:#f92672">`</span>userType<span style="color:#f92672">`</span> varchar(<span style="color:#ae81ff">16</span>) <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,  
  <span style="color:#f92672">`</span>addedTime<span style="color:#f92672">`</span> datetime <span style="color:#66d9ef">NOT</span> <span style="color:#66d9ef">NULL</span>,  
  <span style="color:#66d9ef">PRIMARY</span> <span style="color:#66d9ef">KEY</span> (<span style="color:#f92672">`</span>userId<span style="color:#f92672">`</span>)  
) ENGINE<span style="color:#f92672">=</span>InnoDB <span style="color:#66d9ef">DEFAULT</span> CHARSET<span style="color:#f92672">=</span>utf8;  
  
<span style="color:#75715e">-- ----------------------------  
</span><span style="color:#75715e">-- Records of t_user  
</span><span style="color:#75715e">-- ----------------------------  
</span><span style="color:#75715e"></span><span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> <span style="color:#f92672">`</span>t_user<span style="color:#f92672">`</span> <span style="color:#66d9ef">VALUES</span> (<span style="color:#e6db74">&#39;1&#39;</span>, <span style="color:#e6db74">&#39;爽爽&#39;</span>, <span style="color:#e6db74">&#39;普通&#39;</span>, <span style="color:#e6db74">&#39;2018-01-21 10:20:09&#39;</span>);  
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> <span style="color:#f92672">`</span>t_user<span style="color:#f92672">`</span> <span style="color:#66d9ef">VALUES</span> (<span style="color:#e6db74">&#39;2&#39;</span>, <span style="color:#e6db74">&#39;贵贵&#39;</span>, <span style="color:#e6db74">&#39;普通&#39;</span>, <span style="color:#e6db74">&#39;2017-11-06 10:20:22&#39;</span>);  
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> <span style="color:#f92672">`</span>t_user<span style="color:#f92672">`</span> <span style="color:#66d9ef">VALUES</span> (<span style="color:#e6db74">&#39;3&#39;</span>, <span style="color:#e6db74">&#39;芬芬&#39;</span>, <span style="color:#e6db74">&#39;vip&#39;</span>, <span style="color:#e6db74">&#39;2017-11-13 10:20:42&#39;</span>);  
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> <span style="color:#f92672">`</span>t_user<span style="color:#f92672">`</span> <span style="color:#66d9ef">VALUES</span> (<span style="color:#e6db74">&#39;4&#39;</span>, <span style="color:#e6db74">&#39;思思&#39;</span>, <span style="color:#e6db74">&#39;vip&#39;</span>, <span style="color:#e6db74">&#39;2018-01-21 10:20:55&#39;</span>);  
<span style="color:#66d9ef">INSERT</span> <span style="color:#66d9ef">INTO</span> <span style="color:#f92672">`</span>t_user<span style="color:#f92672">`</span> <span style="color:#66d9ef">VALUES</span> (<span style="color:#e6db74">&#39;5&#39;</span>, <span style="color:#e6db74">&#39;妍妍&#39;</span>, <span style="color:#e6db74">&#39;vip&#39;</span>, <span style="color:#e6db74">&#39;2017-09-17 10:21:28&#39;</span>);
</code></pre></div><p><strong>1、下面是sql语句：</strong></p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#75715e">-- 今天    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">from</span> t_user <span style="color:#66d9ef">where</span> to_days(addedTime) <span style="color:#f92672">=</span> to_days(now());   
<span style="color:#75715e">-- 昨天    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">from</span> t_user <span style="color:#66d9ef">where</span> to_days(NOW()) <span style="color:#f92672">-</span> TO_DAYS(addedTime) <span style="color:#f92672">&lt;=</span> <span style="color:#ae81ff">1</span>;    
<span style="color:#75715e">-- 近7天    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">from</span> t_user <span style="color:#66d9ef">where</span> date_sub(CURDATE(),INTERVAL <span style="color:#ae81ff">7</span> <span style="color:#66d9ef">DAY</span>) <span style="color:#f92672">&lt;=</span> DATE(addedTime);    
<span style="color:#75715e">-- 近30天    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">SELECT</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> DATE_SUB(CURDATE(), INTERVAL <span style="color:#ae81ff">30</span> <span style="color:#66d9ef">DAY</span>) <span style="color:#f92672">&lt;=</span> date(addedTime);  
<span style="color:#75715e">-- 本月    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">SELECT</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">WHERE</span> DATE_FORMAT( addedTime, <span style="color:#e6db74">&#39;%Y%m&#39;</span> ) <span style="color:#f92672">=</span> DATE_FORMAT( CURDATE() , <span style="color:#e6db74">&#39;%Y%m&#39;</span> );  
<span style="color:#75715e">-- 上一月    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">SELECT</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">WHERE</span> PERIOD_DIFF( date_format( now( ) , <span style="color:#e6db74">&#39;%Y%m&#39;</span> ) , date_format( addedTime, <span style="color:#e6db74">&#39;%Y%m&#39;</span> ) ) <span style="color:#f92672">=</span><span style="color:#ae81ff">1</span>;   
<span style="color:#75715e">-- 查询本季度数据    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> QUARTER(addedTime)<span style="color:#f92672">=</span>QUARTER(now());   
<span style="color:#75715e">-- 查询上季度数据    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> QUARTER(addedTime)<span style="color:#f92672">=</span>QUARTER(DATE_SUB(now(),interval <span style="color:#ae81ff">1</span> QUARTER));    
<span style="color:#75715e">-- 查询本年数据    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> <span style="color:#66d9ef">YEAR</span>(addedTime)<span style="color:#f92672">=</span><span style="color:#66d9ef">YEAR</span>(NOW());    
<span style="color:#75715e">-- 查询上年数据    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> <span style="color:#66d9ef">year</span>(addedTime)<span style="color:#f92672">=</span><span style="color:#66d9ef">year</span>(date_sub(now(),interval <span style="color:#ae81ff">1</span> <span style="color:#66d9ef">year</span>));    
<span style="color:#75715e">-- 查询距离当前现在6个月的数据    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> addedTime <span style="color:#66d9ef">between</span> date_sub(now(),interval <span style="color:#ae81ff">6</span> <span style="color:#66d9ef">month</span>) <span style="color:#66d9ef">and</span> now();    
  
<span style="color:#75715e">-- 查询当前这周的数据    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">SELECT</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">WHERE</span> YEARWEEK(date_format(addedTime,<span style="color:#e6db74">&#39;%Y-%m-%d&#39;</span>)) <span style="color:#f92672">=</span> YEARWEEK(now());    
<span style="color:#75715e">-- 查询上周的数据    
</span><span style="color:#75715e"></span><span style="color:#66d9ef">SELECT</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">WHERE</span> YEARWEEK(date_format(addedTime,<span style="color:#e6db74">&#39;%Y-%m-%d&#39;</span>)) <span style="color:#f92672">=</span> YEARWEEK(now())<span style="color:#f92672">-</span><span style="color:#ae81ff">1</span>;    
<span style="color:#75715e">-- 查询上个月的数据     
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> date_format(addedTime,<span style="color:#e6db74">&#39;%Y-%m&#39;</span>)<span style="color:#f92672">=</span>date_format(DATE_SUB(curdate(), INTERVAL <span style="color:#ae81ff">1</span> <span style="color:#66d9ef">MONTH</span>),<span style="color:#e6db74">&#39;%Y-%m&#39;</span>);   
<span style="color:#75715e">-- 查询当前月份的数据  
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> DATE_FORMAT(addedTime,<span style="color:#e6db74">&#39;%Y%m&#39;</span>) <span style="color:#f92672">=</span> DATE_FORMAT(CURDATE(),<span style="color:#e6db74">&#39;%Y%m&#39;</span>);  
<span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> date_format(addedTime,<span style="color:#e6db74">&#39;%Y-%m&#39;</span>)<span style="color:#f92672">=</span>date_format(now(),<span style="color:#e6db74">&#39;%Y-%m&#39;</span>);   
  
<span style="color:#75715e">-- 查询指定时间段的数据  
</span><span style="color:#75715e"></span><span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> addedTime <span style="color:#66d9ef">between</span>  <span style="color:#e6db74">&#39;2017-1-1 00:00:00&#39;</span>  <span style="color:#66d9ef">and</span> <span style="color:#e6db74">&#39;2018-1-1 00:00:00&#39;</span>;     
<span style="color:#66d9ef">select</span> fullName,addedTime <span style="color:#66d9ef">FROM</span> t_user <span style="color:#66d9ef">where</span> addedTime <span style="color:#f92672">&gt;=</span><span style="color:#e6db74">&#39;2017-1-1 00:00:00&#39;</span>  <span style="color:#66d9ef">and</span> addedTime <span style="color:#f92672">&lt;</span> <span style="color:#e6db74">&#39;2018-1-1 00:00:00&#39;</span>;   
</code></pre></div><p><strong>2、归纳：</strong><br>
1、查询时间段内的数据，一般可以用between and 或 &lt;&gt; 来指定时间段。</p>
<p>2、mysql的时间字段类型有：datetime，timestamp，date，time，year。</p>
<p><strong>3、 获取系统当前时间的函数：</strong></p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">        <span style="color:#66d9ef">select</span> CURDATE();
        <span style="color:#66d9ef">select</span> NOW();
</code></pre></div><p><strong>4、获取时间差的函数：</strong></p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">        period_diff()    datediff(date1,date2)      timediff(time1,time2)
</code></pre></div><p><strong>5、日期加减函数：</strong></p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">        date_sub() 

        date_add()     adddate()      addtime()

        period_add(P,N)   
</code></pre></div><p><strong>6、时间格式转化函数：</strong></p>
<div class="highlight"><pre style="color:#f8f8f2;background-color:#272822;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">        date_format(date, format) <span style="color:#960050;background-color:#1e0010">，</span>MySQL日期格式化函数date_format()
        unix_timestamp() 
        str_to_date(str, format) 
        from_unixtime(unix_timestamp, format) <span style="color:#960050;background-color:#1e0010">，</span>MySQL时间戳格式化函数from_unixtime
</code></pre></div>
</div>


    </main>

    
  </body>
</html>
